package util;

import model.NormalUser;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 * @Question NormalUserDao
 * @Date 2022/12/16 21:59
 * @Solution
 */
public class NormalUserDao {
    public int insert(NormalUser normalUser) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection= DBUtil.getConnection();
            String sql = "insert into user values(null,?,?)";
            statement=connection.prepareStatement(sql);
            statement.setString(1,normalUser.getUsername());
            statement.setString(2,normalUser.getPassword());
            int res = statement.executeUpdate();
            return res;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,null);
        }
        return 0;
    }
    public NormalUser getByName(String username) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection= DBUtil.getConnection();
            String sql = "select * from user where username<=>?";
            statement=connection.prepareStatement(sql);
            statement.setString(1,username);
            resultSet = statement.executeQuery();
            if(resultSet.next()) {
                NormalUser normalUser = new NormalUser();
                normalUser.setId(resultSet.getInt("id"));
                normalUser.setUsername(resultSet.getString("username"));
                normalUser.setPassword(resultSet.getString("password"));
                return normalUser;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return null;
    }
    public List<NormalUser> getAll() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<NormalUser> userList=new LinkedList<>();
        try {
            connection=DBUtil.getConnection();
            String sql = "select * from user";
            statement=connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while(resultSet.next()) {
                NormalUser user = new NormalUser();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                userList.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return userList;
    }
    public int deleteById(int id) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int ret=0;
        try {
            connection=DBUtil.getConnection();
            String sql = "delete from user where id=?";
            statement=connection.prepareStatement(sql);
            statement.setInt(1,id);
            ret = statement.executeUpdate();
            return ret;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return ret;
    }
    public int reviseId(int id,int newId) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int ret=0;
        try {
            connection= DBUtil.getConnection();
            String sql = "update user set id=? where id=?";
            statement=connection.prepareStatement(sql);
            statement.setInt(1,newId);
            statement.setInt(2,id);
            ret = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return ret;
    }
    public int reviseName(int id,String newName) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int ret=0;
        try {
            connection= DBUtil.getConnection();
            String sql = "update user set username=? where id=?";
            statement=connection.prepareStatement(sql);
            statement.setString(1,newName);
            statement.setInt(2,id);
            ret = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return ret;
    }
    public int revisePassword(int id,String newPassword) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int ret=0;
        try {
            connection= DBUtil.getConnection();
            String sql = "update user set password=? where id=?";
            statement=connection.prepareStatement(sql);
            statement.setString(1,newPassword);
            statement.setInt(2,id);
            ret = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return ret;
    }
}
